﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BoutiquePOS.DAL.DAO;

namespace BoutiquePOS.DAL.Gateway
{  
    public class PurchaseGateway : DbGateway
    {
        private List<PurchaseDtls> purchases;
        PurchaseDtls aPurchase;
        private PurchaseDtls purchase;

        public void SavePurchaseDtls(List<PurchaseDtls> purchaseDtlses)
        {
            try
            {
                ConnectionObj.Open();
                foreach (PurchaseDtls aPurchase in purchaseDtlses)
                {
                    string query = String.Format("INSERT INTO PosPurchaseDtls VALUES(@purchaseId,@productId,@qty,@freeQty,@paymentMode,@tradePrice,@totalTradePrice,@totalVat,@discountPer,@discountAmount,@totalFlatprice,@batchNo,@expireDate,@vat)");  
                    CommandObj.CommandText = query;
                    CommandObj.Parameters.Clear();
                    CommandObj.Parameters.Add("@purchaseId", SqlDbType.Int);
                    CommandObj.Parameters["@purchaseId"].Value = aPurchase.PurchaseId; 
                    CommandObj.Parameters.Add("@productId", SqlDbType.Int);
                    CommandObj.Parameters["@productId"].Value = aPurchase.AProduct.ProductId;
                    CommandObj.Parameters.Add("@qty", SqlDbType.Int);
                    CommandObj.Parameters["@qty"].Value = aPurchase.PurchaseQty;
                    CommandObj.Parameters.Add("@freeQty", SqlDbType.Int);
                    CommandObj.Parameters["@freeQty"].Value = aPurchase.PurchaseFreeQty; 
                    CommandObj.Parameters.Add("@paymentMode", SqlDbType.Int);
                    CommandObj.Parameters["@paymentMode"].Value = aPurchase.PaymentMode;
                    CommandObj.Parameters.Add("@tradePrice", SqlDbType.Decimal);
                    CommandObj.Parameters["@tradePrice"].Value = aPurchase.TradePrice;
                    CommandObj.Parameters.Add("@totalTradePrice", SqlDbType.Decimal);
                    CommandObj.Parameters["@totalTradePrice"].Value = aPurchase.TotalTradePrice;
                    CommandObj.Parameters.Add("@totalVat", SqlDbType.Decimal);
                    CommandObj.Parameters["@totalVat"].Value = aPurchase.TotalVAt;
                    CommandObj.Parameters.Add("@discountPer", SqlDbType.Decimal);
                    CommandObj.Parameters["@discountPer"].Value = aPurchase.DiscountPer;
                    CommandObj.Parameters.Add("@discountAmount", SqlDbType.Decimal);
                    CommandObj.Parameters["@discountAmount"].Value = aPurchase.TotalDiscountAmount;
                    //= For Future Need =//
                    CommandObj.Parameters.Add("@totalFlatprice", SqlDbType.Decimal); 
                    CommandObj.Parameters["@totalFlatprice"].Value = 0.0;
                    CommandObj.Parameters.Add("@batchNo", SqlDbType.VarChar); 
                    CommandObj.Parameters["@batchNo"].Value = "b-1";
                    CommandObj.Parameters.Add("@expireDate", SqlDbType.VarChar); 
                    CommandObj.Parameters["@expireDate"].Value = "20130708";
                    //= VAT Amount for single unit product, without show in gv =//
                    CommandObj.Parameters.Add("@vat", SqlDbType.Decimal);
                    CommandObj.Parameters["@vat"].Value = aPurchase.AProduct.Vat;
                    CommandObj.ExecuteNonQuery();
                }
                ConnectionObj.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Sales couldn't saved", ex);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        public void SavePurchase(Purchase apurchase)
        {
            try
            {
                ConnectionObj.Open();
                string query1 = String.Format("INSERT INTO PosPurchase VALUES(@purchaseNo,@purchaseDate,@supplierId,@totalNetAmount,@challanNo)");
                CommandObj.CommandText = query1;
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@purchaseNo", SqlDbType.VarChar);
                CommandObj.Parameters["@purchaseNo"].Value = apurchase.PurchaseNo;
                CommandObj.Parameters.Add("@purchaseDate", SqlDbType.VarChar);
                CommandObj.Parameters["@purchaseDate"].Value = apurchase.PurchaseDate;
                CommandObj.Parameters.Add("@supplierId", SqlDbType.Int);
                CommandObj.Parameters["@supplierId"].Value = apurchase.ASupplier.SupplierId;
                CommandObj.Parameters.Add("@totalNetAmount", SqlDbType.Decimal);
                CommandObj.Parameters["@totalNetAmount"].Value = apurchase.TotalNetAmount;
                CommandObj.Parameters.Add("@challanNo", SqlDbType.VarChar);
                CommandObj.Parameters["@challanNo"].Value = apurchase.ChallanNo;                
                CommandObj.ExecuteNonQuery();
                ConnectionObj.Close();          
            }
            catch (Exception ex)
            {
                throw new Exception("Error in data");
            }
        }

        public void SaveSupplierPayment(SupplierPayment aSupplierPayment)
        {
            try
            {
                ConnectionObj.Open();
                string query2 = String.Format("INSERT INTO PosSupplierPayment VALUES(@purchaseId,@paymentDate,@paidAmount)");
                CommandObj.CommandText = query2;
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@purchaseId", SqlDbType.VarChar);
                CommandObj.Parameters["@purchaseId"].Value = aSupplierPayment.APurchase.PurchaseId;
                CommandObj.Parameters.Add("@paymentDate", SqlDbType.VarChar);
                CommandObj.Parameters["@paymentDate"].Value = aSupplierPayment.PaymentDate;
                CommandObj.Parameters.Add("@paidAmount", SqlDbType.Decimal);
                CommandObj.Parameters["@paidAmount"].Value = aSupplierPayment.PaidAmount;
                CommandObj.ExecuteNonQuery();
                ConnectionObj.Close();
            }
            catch (Exception)
            {   
                throw;
            }
        }

        public List<PurchaseDtls> GetAllPurchaseDtls()
        {
            List<PurchaseDtls> PurchaseDtlss = new List<PurchaseDtls>();
            try
            {
                ConnectionObj.Open();
                string query = String.Format("SELECT * FROM PosPurchaseDtls");

                CommandObj.CommandText = query;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    PurchaseDtls aPurchaseDtls = new PurchaseDtls();

                    aPurchaseDtls.PurchaseReceiptNo = reader["PurchaseDtlsName"].ToString();
                    PurchaseDtlss.Add(aPurchaseDtls);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred during PurchaseDtls loading from your system.", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return PurchaseDtlss;
        }

        public PurchaseDtls FindPurchaseById(int purchaseId)
        {
            try
            {
                string search = String.Format("Select * FROM PosPurchase WHERE PurchaseId=@PurchaseId");
                ConnectionObj.Open();
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@PurchaseId", SqlDbType.Int);
                CommandObj.Parameters["@PurchaseId"].Value = purchaseId;
                CommandObj.CommandText = search;
                SqlDataReader sqlDataReader = CommandObj.ExecuteReader();
                PurchaseDtls aPurchase = new PurchaseDtls();
                if (sqlDataReader != null)
                {
                    while (sqlDataReader.Read())
                    {
                        //aPurchase.PurchaseId = Convert.ToInt32(sqlDataReader["PurchaseId"]);
                        aPurchase.PurchaseReceiptNo = sqlDataReader["PurchaseName"].ToString();
                        return aPurchase;
                    }
                }
                return null;
            }

            catch (Exception exception)
            {
                throw new Exception("Could not Found Purchase", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        public string GetPurchaseAutoCompleteData(int categoryId)
        {
            string suggestionList = "";
            try
            {
                string queryString = string.Format("SELECT productBrandName  FROM  PosProduct Where categoryId={0}", categoryId);

                CommandObj.Connection = ConnectionObj;
                CommandObj.CommandText = queryString;
                ConnectionObj.Open();
                SqlDataReader reader = CommandObj.ExecuteReader();

                while (reader.Read())
                {
                    if (string.IsNullOrEmpty(suggestionList))
                    {
                        suggestionList += "\"" + reader["productBrandName"].ToString() + "\"";
                    }
                    else
                    {
                        suggestionList += ", \"" + reader["productBrandName"].ToString() + "\"";
                    }
                }
            }

            catch (Exception exception)
            {
                throw new Exception("Couldn't Loaded Data", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return suggestionList;
        }

        public string NextID(string yearAndMonth)
        {
            try
            {
                var table = new DataTable();

                string sql = "Select max(PurchaseNo) From PosPurchase";
                CommandObj.Parameters.Clear();
                CommandObj.CommandText = sql;
                ConnectionObj.Open();
                SqlDataAdapter ad = new SqlDataAdapter(CommandObj);
                ad.Fill(table);
                string id = table.Rows[0][0].ToString();
                if (id == "")
                {
                    string dd = "000001";
                    return yearAndMonth + dd;
                }
                else
                {
                    string temp;
                    int Nonzero;
                    int length;
                    string currentMaxId = table.Rows[0][0].ToString().Trim();
                    string lastSixDigit = currentMaxId.Substring(6, 6);
                    temp = lastSixDigit.ToString().Trim();
                    Nonzero = Convert.ToInt32(lastSixDigit.ToString().Trim());
                    int ssss = Nonzero + 1;
                    length = temp.Length - ssss.ToString().Length;
                    string mo = (temp.Substring(0, length) + ssss.ToString());
                    return yearAndMonth + mo;

                    //temp = table.Rows[0][0].ToString().Trim();
                    //Nonzero = Convert.ToInt32(table.Rows[0][0].ToString().Trim());
                    //int ssss = Nonzero + 1;
                    //length = temp.Length - ssss.ToString().Length;
                    //string mo = (temp.Substring(0, length) + ssss.ToString());               
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Cann't Create Next Id", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        internal int ReturnPurchaseId(string purchaseNo)
        {
            int id = 0;
            try
            {
                string search = String.Format("Select * FROM PosPurchase WHERE purchaseNo='{0}'", purchaseNo);
                ConnectionObj.Open();
                CommandObj.CommandText = search;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    id = Convert.ToInt32(reader[0]);
                }

                return id;
            }
            catch (Exception exception)
            {
                throw new Exception("Could not Found PurchaseDtls", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }


    }
}